package org.compiere.process;

import java.io.File;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.NumberFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
import java.util.logging.Level;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;
import jxl.write.DateFormat;
import jxl.write.DateTime;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.WritableCellFormat;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import org.adempiere.exceptions.AdempiereException;
import org.compiere.apps.ADialog;
import org.compiere.model.MAttachmentEntry;
import org.compiere.model.X_CMS_Contract;
import org.compiere.util.CLogger;
import org.compiere.util.DB;
import org.compiere.util.Env;
import org.compiere.util.Msg;

import extend.org.compiere.hrm.ExcelCell;
import extend.org.compiere.utils.Util;

public class CMS_RegisterOfContracts extends SvrProcess {

	// Current context
		private Properties m_ctx;
		
		private ProcessInfo pi;
		//User
		private int			AD_User_ID = 0;
		//Role
		private int			AD_Role_ID = 0;
		// Client
		private int			AD_Client_ID = 0;
		// Organization
		private int			AD_Org_ID = 0;
		// C_BPartner
		private int			C_BPartner_ID = 0;
		// HR_Department
		private int			HR_Department_ID = 0;
		// ContractType
		private int			CMS_ContractType_ID = 0;
		// StatusType
		private int			CMS_statusestype_ID = 0;
		// ContractDateStart
		private Timestamp 	CMS_ContractDateStart = null;
		// ContractDateEnd
		private Timestamp 	CMS_ContractDateEnd = null;
		
		private Timestamp 	DateFromS = null;
		// ContractDateEnd
		private Timestamp 	DateFromE = null;
		
		//FilePath
		private String path = "";
		
		private ArrayList<Integer> cms_contracts = new ArrayList<Integer>();
		
		private static CLogger	s_log	= CLogger.getCLogger (CMS_ContractsCheck.class);
		
		@Override
		protected void prepare() {
						
			log.info("");
			m_ctx = Env.getCtx();
			pi = getProcessInfo();
			ProcessInfoParameter[] para = getParameter();

			for(int i = 0; i < para.length; i++) {
				String name = para[i].getParameterName();			
				if (para[i].getParameter() == null);
				else if (name.equals("AD_Client_ID"))
					AD_Client_ID = ((BigDecimal)para[i].getParameter()).intValue();
				else if (name.equals("AD_Org_ID"))
					AD_Org_ID = ((BigDecimal)para[i].getParameter()).intValue();
				else if (name.equals("C_BPartner_ID"))
					C_BPartner_ID = ((BigDecimal)para[i].getParameter()).intValue();
				else if (name.equals("HR_Department_ID"))
					HR_Department_ID = ((BigDecimal)para[i].getParameter()).intValue();
				else if (name.equals("cms_contractstype_ID"))
					CMS_ContractType_ID = ((BigDecimal)para[i].getParameter()).intValue();
				else if (name.equals("cms_statusestype_ID"))
					CMS_statusestype_ID = ((BigDecimal)para[i].getParameter()).intValue();
				else if (name.equals("CMS_Contract_CreatDateStart"))
					CMS_ContractDateStart = (Timestamp)para[i].getParameter();		
				else if (name.equals("CMS_Contract_CreatDateEnd"))
					CMS_ContractDateEnd = (Timestamp)para[i].getParameter();
				else if (name.equals("DateFromS"))
					DateFromS = (Timestamp)para[i].getParameter();
				else if (name.equals("DateFromE"))
					DateFromE = (Timestamp)para[i].getParameter();				
				else if (name.toLowerCase().endsWith("directory"))
					path = (String)para[i].getParameter();
				else
				{
					log.log(Level.SEVERE, "Unknown Parameter: " + name);
				}				
			}
			if(path.isEmpty()){
				path = Util.localFilePath;
			}else
				path = path + System.getProperty("file.separator");
			
			AD_Role_ID = Env.getAD_Role_ID(getCtx());
			AD_User_ID = Env.getAD_User_ID(getCtx());
		}

		@Override
		protected String doIt() throws Exception {
			
			String patternName = "ReportPattern";
			
			int count = getCMS_Contract_ID();
			
			if(count == 0)
				return "Для вашей учетной записи в системе нет договоров";
			//get attachment from the process
			MAttachmentEntry entry = Util.getAttachment(pi, m_ctx, patternName);
			if(entry == null)
				throw new AdempiereException(Msg.translate(m_ctx, "NotFoundTemplate")+" "+patternName);
			
			// We define a path to generate
			String fileExtension = entry.getName().substring(entry.getName().lastIndexOf("."),entry.getName().length());
			StringBuffer fullPath = new StringBuffer(path);		
			fullPath.append("Temporary CMS Register Sheets");		
			fullPath.append(fileExtension);
			
			File templateCopy = new File(fullPath.toString());
	        File temporaryFile = entry.getFile(Util.localFilePath+patternName);
			Workbook tableBook = null;
			WritableWorkbook copy = null;
			WritableSheet sheet = null;
			
			try {
				tableBook = Workbook.getWorkbook(temporaryFile);
				copy = Workbook.createWorkbook(templateCopy, tableBook);
				sheet = copy.getSheet(0);
	        } 
			catch (Exception e) {
	        	ADialog.error(999, null, "Error tableBook. " + e.getMessage());
	        	return "Error tableBook.";
	        }
			
			ExcelCell cellStart =  Util.getCellStart(tableBook,">>");
			
			// Style for value column
			WritableCellFormat nn = new WritableCellFormat();
			nn.setBorder(Border.LEFT, BorderLineStyle.THIN, Colour.BLACK);
			nn.setBorder(Border.RIGHT, BorderLineStyle.THIN, Colour.BLACK);
			nn.setBorder(Border.BOTTOM, BorderLineStyle.THIN, Colour.BLACK);
			nn.setBorder(Border.TOP, BorderLineStyle.THIN, Colour.BLACK);
			nn.setAlignment(Alignment.CENTRE);
			nn.setVerticalAlignment(VerticalAlignment.CENTRE);
			nn.setWrap(true);
		    
			
		    WritableCellFormat valuestyle = new WritableCellFormat();
		    valuestyle.setBorder(Border.LEFT, BorderLineStyle.THIN, Colour.BLACK);
		    valuestyle.setBorder(Border.RIGHT, BorderLineStyle.THIN, Colour.BLACK);
		    valuestyle.setBorder(Border.BOTTOM, BorderLineStyle.THIN, Colour.BLACK);
		    valuestyle.setBorder(Border.TOP, BorderLineStyle.THIN, Colour.BLACK);
		    valuestyle.setAlignment(Alignment.LEFT);
		    valuestyle.setVerticalAlignment(VerticalAlignment.CENTRE);
		    valuestyle.setWrap(true);
		    
		    WritableCellFormat docNumStyle = new WritableCellFormat();
		    docNumStyle.setBorder(Border.LEFT, BorderLineStyle.THIN, Colour.BLACK);
		    docNumStyle.setBorder(Border.RIGHT, BorderLineStyle.THIN, Colour.BLACK);
		    docNumStyle.setBorder(Border.BOTTOM, BorderLineStyle.THIN, Colour.BLACK);
		    docNumStyle.setBorder(Border.TOP, BorderLineStyle.THIN, Colour.BLACK);
		    docNumStyle.setAlignment(Alignment.CENTRE);
		    docNumStyle.setVerticalAlignment(VerticalAlignment.CENTRE);
		    docNumStyle.setWrap(true);
		    
		    // Style for name column
		    WritableCellFormat namestyle = new WritableCellFormat();
		    namestyle.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
		    namestyle.setAlignment(Alignment.LEFT);
		    namestyle.setVerticalAlignment(VerticalAlignment.CENTRE);
		    namestyle.setWrap(true);
		    //Style for number column
		    WritableCellFormat numcolstyle = new WritableCellFormat();
		    numcolstyle.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
		    numcolstyle.setAlignment(Alignment.RIGHT);
		    numcolstyle.setVerticalAlignment(VerticalAlignment.CENTRE);
		    numcolstyle.setWrap(true);
		    //Style for last number column
		    WritableCellFormat lastNumcolstyle = new WritableCellFormat();
		    lastNumcolstyle.setBorder(Border.RIGHT, BorderLineStyle.THIN, Colour.BLACK);
		    lastNumcolstyle.setBorder(Border.LEFT, BorderLineStyle.THIN, Colour.BLACK);
		    lastNumcolstyle.setBorder(Border.TOP, BorderLineStyle.THIN, Colour.BLACK);
		    lastNumcolstyle.setBorder(Border.BOTTOM, BorderLineStyle.THIN, Colour.BLACK);
		    lastNumcolstyle.setAlignment(Alignment.RIGHT);
		    lastNumcolstyle.setVerticalAlignment(VerticalAlignment.CENTRE);
		    lastNumcolstyle.setWrap(true);
		    //Style for total row
		    WritableCellFormat totalrowstyle = new WritableCellFormat();
		    totalrowstyle.setBorder(Border.ALL, BorderLineStyle.MEDIUM, Colour.BLACK);
		    totalrowstyle.setAlignment(Alignment.RIGHT);
		    totalrowstyle.setVerticalAlignment(VerticalAlignment.CENTRE);
		    totalrowstyle.setWrap(true);

		    DateFormat customDateFormat = new DateFormat ("dd.MM.yy");
		    WritableCellFormat dateFormat = new WritableCellFormat (customDateFormat);
		    dateFormat.setBorder(Border.LEFT, BorderLineStyle.THIN, Colour.BLACK);
		    dateFormat.setBorder(Border.RIGHT, BorderLineStyle.THIN, Colour.BLACK);
		    dateFormat.setBorder(Border.TOP, BorderLineStyle.THIN, Colour.BLACK);
		    dateFormat.setBorder(Border.BOTTOM, BorderLineStyle.THIN, Colour.BLACK);
		    dateFormat.setAlignment(Alignment.CENTRE);
		    dateFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
		    
		    int col = cellStart.getC()+1; 
		    int row = cellStart.getR();
		    
		    TempCMS_Contract[] lines = GetLines();
		    		    
			// Types of cells for
			Label label;
			Number number;
			DateTime time;
			
			int buffer = 0;
			
			String expenceType = "SELECT CMS_ContractsType_ID FROM CMS_ContractsType WHERE LOWER(name) LIKE '%расходный%'";
			String profitType = "SELECT CMS_ContractsType_ID FROM CMS_ContractsType WHERE LOWER(name) LIKE '%доходный%'";
			
			int expence = DB.getSQLValue(get_TrxName(), expenceType);
			int profit = DB.getSQLValue(get_TrxName(), profitType);
			
			if(expence == -1 | profit == -1)
			{
				throw new Exception("Система не нашла доступных видов договоров");
			}
			
			NumberFormat format = NumberFormat.getInstance();
			format.setMinimumFractionDigits(2);
			format.setMaximumFractionDigits(4);
			
			int counter = row;
			
		    for(int i = row; (i-row) < lines.length; i++) {
		    	if(!cms_contracts.contains(lines[i-row].CMS_Contract_ID))
		    			continue;
		    	number = new Number(col-1, counter+buffer, counter-row+1, nn);  	  
		    	sheet.addCell(number);
		    	label = new Label(col, counter+buffer, lines[i-row].ContractNumber, docNumStyle);        
		    	sheet.addCell(label);
		    	if(lines[i-row].ContractDate != null) {
		    		time = new DateTime(col+1, counter+buffer, new java.sql.Date(lines[i-row].ContractDate.getTime()), dateFormat);      
		    		sheet.addCell(time);
		    	}
		    	else sheet.addCell(new Label(col+1,counter+buffer,"",valuestyle));

		    	label = new Label(col+2, counter+buffer, lines[i-row].C_BPartner, valuestyle);     
		    	sheet.addCell(label);    	   
		    	label = new Label(col+3, counter+buffer, lines[i-row].Description, valuestyle);     
		    	sheet.addCell(label);   	   
//		    	number = new Number(col+4, counter+buffer,  lines[i-row].Summary, numcolstyle);  	  
//		    	sheet.addCell(number);    	   
		    	number = new Number(col+4, counter+buffer,  lines[i-row].Summary.setScale(2, RoundingMode.HALF_UP).doubleValue(), numcolstyle);  	  
		    	sheet.addCell(number);  
		    	label = new Label(col+5, counter+buffer, lines[i-row].ContractType, docNumStyle);     
		    	sheet.addCell(label);   	   
		    	
		    	if(lines[i-row].DateFrom != null) {
		    		time = new DateTime(col+6, counter+buffer, new java.sql.Date(lines[i-row].DateFrom.getTime()), dateFormat);
		    		sheet.addCell(time);
		    	}
		    	else {
		    		label = new Label(col+6, counter+buffer, "", valuestyle);
		    		sheet.addCell(label);
		    	}
		    	
		    	if(lines[i-row].A_Expired_Date != null) {
		    		time = new DateTime(col+7, counter+buffer, new java.sql.Date(lines[i-row].A_Expired_Date.getTime()), dateFormat);
		    		sheet.addCell(time);
		    	}
		    	else {
		    		label = new Label(col+7, counter+buffer, "", valuestyle);
		    		sheet.addCell(label);
		    	}
		    	
		    	if(lines[i-row].BeginDate != null) {
		    		time = new DateTime(col+8, counter+buffer, new java.sql.Date(lines[i-row].BeginDate.getTime()), dateFormat);
		    		sheet.addCell(time);
		    	}
		    	else {
		    		label = new Label(col+8, counter+buffer, "", valuestyle);
		    		sheet.addCell(label);
		    	}
		    	
		    	if(lines[i-row].EndDate != null) {
		    		time = new DateTime(col+9, counter+buffer, new java.sql.Date(lines[i-row].EndDate.getTime()), dateFormat);
		    		sheet.addCell(time);
		    	}
		    	else {
		    		label = new Label(col+9, counter+buffer, "", valuestyle);
		    		sheet.addCell(label);
		    	}
		    	
		    	label = new Label(col+10, counter+buffer, lines[i-row].StatusType, docNumStyle); 
		    	sheet.addCell(label);
		    	number = new Number(col+11, counter+buffer, lines[i-row].TotalGrand.setScale(2, RoundingMode.HALF_UP).doubleValue(), numcolstyle); 
		    	sheet.addCell(number);
		    	
		    	if(lines[i-row].Summary.doubleValue() != 0) {
		    		number = new Number(col+12, counter+buffer, (lines[i-row].TotalGrand.divide(lines[i-row].Summary, 4,RoundingMode.HALF_UP)).doubleValue()*100, numcolstyle); 
		    		sheet.addCell(number);
		    	}
		    	else {
		    		number = new Number(col+12, counter+buffer, new BigDecimal(0.0).setScale(2).doubleValue(), numcolstyle);
		    		sheet.addCell(number);
		    	}
		    	
		    	if(lines[i-row].PayAmt.setScale(2).doubleValue() == 0.0)
		    	{
		    		if(lines[i-row].ContractType != null)
		    		{
		    			if(lines[i-row].ContractType.compareTo("Расходный") == 0)
		    				lines[i-row].PayAmt = getPayAmt(lines[i-row].CMS_Contract_ID, "331", "161");
		    			if(lines[i-row].ContractType.compareTo("Доходный") == 0)
		    				lines[i-row].PayAmt = getPayAmt(lines[i-row].CMS_Contract_ID, "351", "121");		    		
		    		}
		    	}
		    	
		    	number = new Number(col+13, counter+buffer, lines[i-row].PayAmt.setScale(2).doubleValue(), numcolstyle); 
		    	sheet.addCell(number);	    
		    	
		    	if(lines[i-row].Summary.doubleValue() != 0) {
		    		number = new Number(col+14, counter+buffer, (lines[i-row].PayAmt.divide(lines[i-row].Summary, 4, RoundingMode.HALF_UP)).doubleValue()*100, numcolstyle); 
		    		sheet.addCell(number);	  
		    	}
		    	else {
		    		number = new Number(col+14, counter+buffer, new BigDecimal(0.0).setScale(2).doubleValue(), numcolstyle);
		    		sheet.addCell(number);
		    	}   		    	
		    	label = new Label(col+15, counter+buffer, lines[i-row].ProcurementType, docNumStyle); 
		    	sheet.addCell(label);
		    	
		    	label = new Label(col+16, counter+buffer, lines[i-row].Department, docNumStyle); 
		    	sheet.addCell(label);
		    	
		    	number = new Number(col+17, counter+buffer, lines[i-row].KazContent.setScale(2).doubleValue(), numcolstyle); 
		    	sheet.addCell(number);
		    	buffer += SetLines(col, counter+buffer+1, sheet, lines[i-row].CMS_Contract_ID, nn, valuestyle, namestyle, numcolstyle, lastNumcolstyle, totalrowstyle, dateFormat);
		    	counter++;
		    }
		    
		    copy.write();
		    copy.close();
		    tableBook.close();
		    temporaryFile.delete();
			
		    try
		    {    
		   		String fileName = fullPath.toString();
		   		String command = "excel \""+fileName+"\"";
		   		Runtime.getRuntime().exec("cmd /c start "+command);
		   	}
		    catch(Exception e)
		    {
		    	e.printStackTrace();
		   	}
				
		   return Msg.translate(m_ctx, "Success");
		}
		
		private BigDecimal getPayAmt(int CMS_Contract, String f331, String f161)
		{
			StringBuffer sql = new StringBuffer();
			sql.append("Select e.Value, SUM(f.AmtSourceDr) AS debit, SUM(f.AmtSourceCr) as credit ");
			sql.append("\nFROM Adempiere.Fact_Acct f INNER JOIN C_ElementValue e ON f.Account_ID = e.C_ElementValue_ID ");
			sql.append("\nINNER JOIN Adempiere.CMS_Contract c ON f.UserElement1_ID = c.CMS_Contract_ID ");
			sql.append("\nWHERE (e.Value LIKE '"+f161+"%' OR e.Value LIKE '"+f331+"%') AND c.CMS_Contract_ID = ");
			sql.append(CMS_Contract);
			sql.append("\nGROUP BY e.Value, c.CMS_Contract_ID ");
			sql.append("\nORDER BY c.CMS_Contract_ContractNum");

			PreparedStatement pstmt = null;
			ResultSet rs = null;
			
			double sum331c = 0.0;
			double sum331d = 0.0;
			
			double sum161 = 0.0;
			
			try 
			{				
				pstmt = DB.prepareStatement(sql.toString(), get_TrxName());
				rs = pstmt.executeQuery();	

				while(rs.next())
				{			
					if(rs.getString(1).startsWith("3"))
					{
						sum331d += rs.getDouble(2);
						sum331c += rs.getDouble(3);
					}
					if(rs.getString("value").startsWith("1"))
					{
						sum161 += rs.getDouble(3);						
					}						
				}
			} 
			catch (SQLException e) {log.log(Level.SEVERE, "CMS_RegisterOfContract: " + e.toString());}
			finally{DB.close(rs, pstmt); rs = null; pstmt = null;}
						
			if(sum331c == sum331d)
			{
				return new BigDecimal(sum161);
			}
			else
			{
				return new BigDecimal(sum331c - sum331d).setScale(3, RoundingMode.HALF_UP);
			}
			
		}
		
		@SuppressWarnings("deprecation")
		private int getCMS_Contract_ID()
		{
			StringBuffer sql = new StringBuffer();
			
			sql.append("Select cms_roleaccess(");
			sql.append(AD_User_ID + ", ");
			sql.append(AD_Role_ID + ")");
			
			PreparedStatement pstmt = null;
			ResultSet rs = null;
			
			try
			{
				pstmt = DB.prepareStatement(sql.toString());
				rs = pstmt.executeQuery();
				
				while(rs.next())
				{
					cms_contracts.add(rs.getInt(1));
				}
			}
			catch(Exception e) {log.log(Level.SEVERE, "CMS_RegisterOfContract: " + e.toString());}
			finally{DB.close(rs, pstmt); rs = null; pstmt = null;}
			
			return cms_contracts.size();
		}
		
		private TempCMS_Contract[] GetLines() {
			TempCMS_Contract temp = null;
			List<TempCMS_Contract> list = new ArrayList<TempCMS_Contract>();
			
			PreparedStatement pstmt = null;
			ResultSet rs = null;

			StringBuffer sqlQuery = new StringBuffer();
			
			sqlQuery.append("SELECT c.CMS_Contract_ContractNum, c.CMS_Contract_CreatDate, b.Name, c.Description, c.ContractSummary, ");
			sqlQuery.append("\nt.Name, c.DateFrom, c.A_Expired_Date, c.BeginningDateExecution, ");
			sqlQuery.append("\nc.EndDateExecution, r.Description, p.Name, hr.Name,");
			sqlQuery.append("\n(SELECT SUM(inv.GrandTotal) FROM Adempiere.C_Invoice inv WHERE inv.CMS_Contract_ID = c.CMS_Contract_ID AND inv.DocStatus = 'CO' AND inv.isActive = 'Y'), ");
			sqlQuery.append("\n(SELECT SUM(p2.PayAmt) FROM Adempiere.C_Payment p2 WHERE p2.CMS_Contract_ID = c.CMS_Contract_ID AND p2.DocStatus='CO' GROUP BY p2.CMS_Contract_ID), ");
			sqlQuery.append("\nf.KzContent, c.CMS_Contract_ID ");
			sqlQuery.append("\nFROM Adempiere.CMS_Contract c LEFT JOIN Adempiere.C_BPartner b ON c.C_BPartner_ID = b.C_Bpartner_ID ");
			sqlQuery.append("\nLEFT JOIN Adempiere.CMS_ContractsType t ON c.CMS_ContractsType_ID = t.CMS_ContractsType_ID ");
			sqlQuery.append("\nLEFT JOIN Adempiere.CMS_ProcurementsType p ON c.CMS_ProcurementsType_ID = p.CMS_ProcurementsType_ID ");
			sqlQuery.append("\nLEFT JOIN Adempiere.HR_Department hr ON c.HR_Department_ID = hr.HR_Department_ID ");
			sqlQuery.append("\nLEFT JOIN Adempiere.CMS_KazSertificate f ON c.CMS_Contract_ID = f.CMS_Contract_ID ");
			sqlQuery.append("\nINNER JOIN ADEMPIERE.AD_Ref_List r ON r.AD_Reference_ID = " + X_CMS_Contract.DOCACTION_AD_Reference_ID + " AND r.Value = c.DocStatus ");
			sqlQuery.append("\nWHERE c.AD_Client_ID = ? AND c.AD_Org_ID = ? ");
			
			if(C_BPartner_ID != 0)
				sqlQuery.append(" AND c.C_BPartner_ID = " + C_BPartner_ID);
			if(HR_Department_ID != 0)
				sqlQuery.append(" AND c.HR_Department_ID = " + HR_Department_ID);
			if(CMS_ContractType_ID != 0)
				sqlQuery.append(" AND c.cms_contractstype_ID = " + CMS_ContractType_ID);
			if(CMS_statusestype_ID != 0)
				sqlQuery.append(" AND c.CMS_statusestype_ID = " + CMS_statusestype_ID);
			if(CMS_ContractDateStart != null)
				sqlQuery.append(" AND c.CMS_Contract_CreatDate >= '" + CMS_ContractDateStart + "'");
			if(CMS_ContractDateEnd != null)
				sqlQuery.append(" AND c.CMS_Contract_CreatDate <= '" + CMS_ContractDateEnd + "'");
			if(DateFromS != null)
				sqlQuery.append(" AND c.DateFrom >= '" + DateFromS + "'");
			if(DateFromE != null)
				sqlQuery.append(" AND c.A_Expired_Date <= '" + DateFromE + "'");
			
			sqlQuery.append(" ORDER BY c.cms_contract_id");
			
			try{
				pstmt = DB.prepareStatement(sqlQuery.toString(), get_TrxName());
				pstmt.setInt(1, AD_Client_ID);
				pstmt.setInt(2, AD_Org_ID);
				rs = pstmt.executeQuery();
				
				while(rs.next())
				{
					temp = new TempCMS_Contract();
					temp.ContractNumber = rs.getString(1);
					temp.ContractDate = rs.getTimestamp("CMS_Contract_CreatDate");
					temp.C_BPartner = rs.getString(3);
					temp.Description = rs.getString(4);
					if(rs.getBigDecimal(5) != null)
						temp.Summary = rs.getBigDecimal(5);
					else
						temp.Summary = new BigDecimal(0.0);
					
					temp.ContractType = rs.getString(6);
					temp.DateFrom = rs.getTimestamp("DateFrom");
					temp.A_Expired_Date = rs.getTimestamp(8);
					temp.BeginDate = rs.getTimestamp(9);
					temp.EndDate = rs.getTimestamp(10);
					temp.StatusType = rs.getString(11);
					temp.ProcurementType = rs.getString(12);
					temp.Department = rs.getString(13);

					if(rs.getBigDecimal(14) != null)
						temp.TotalGrand = rs.getBigDecimal(14);
					else temp.TotalGrand = new BigDecimal(0.0);
					
					if(rs.getBigDecimal(15) != null)
						temp.PayAmt = rs.getBigDecimal(15);
					else temp.PayAmt = new BigDecimal(0.0);
					
					if(rs.getBigDecimal(16) != null)
						temp.KazContent = rs.getBigDecimal(16);					
					else temp.KazContent = new BigDecimal(0.0);
					
					temp.CMS_Contract_ID = rs.getInt(17);
					
					list.add(temp);
				}
			}
			catch(Exception e)
			{
				s_log.log(Level.SEVERE, "Temp ", e);
			}
			finally
			{
				DB.close(rs, pstmt);
				rs = null; pstmt = null;
			}
			
			return list.toArray(new TempCMS_Contract[list.size()]);
		}
		
		private int SetLines(int x, int y, WritableSheet sheet, int CMS_Contract_ID,
							WritableCellFormat nn, WritableCellFormat valueStyle, WritableCellFormat namestyle,
							WritableCellFormat numColstyle, WritableCellFormat lastNumcolstyle, WritableCellFormat totalrowstyle,
							WritableCellFormat dateCellFormat)
		{	
			WritableCellFormat valuestyle = new WritableCellFormat(valueStyle);
			WritableCellFormat numcolstyle = new WritableCellFormat(numColstyle);
			WritableCellFormat dateFormat = new WritableCellFormat(dateCellFormat);
			try
			{				
				valuestyle.setBackground(Colour.IVORY);
				numcolstyle.setBackground(Colour.IVORY);
				dateFormat.setBackground(Colour.IVORY);
			}
			catch(Exception e){
				log.log(Level.SEVERE,e.toString());
			}
			
		    StringBuffer sql = new StringBuffer();
		    sql.append("SELECT b.Name, l.DateSigned, l.Description, l.Summary, l.DateFrom, l.A_Expired_Date, l.BeginningDateExecution, l.EndDateExecution, c.DocumentNo, l.name as LineDocNo ");
			sql.append("\nFROM Adempiere.CMS_ContractLine l LEFT JOIN Adempiere.CMS_Contract c ON l.CMS_Contract_ID = c.CMS_Contract_ID ");
			sql.append("\nLEFT JOIN Adempiere.C_BPartner b ON l.C_BPartner_ID = b.C_BPartner_ID ");
			sql.append("\nWHERE c.CMS_Contract_ID = " + CMS_Contract_ID);
		
		    PreparedStatement pstmt = null;
		    ResultSet rs = null;
		    
		    Number number;
		    Label label;
		    DateTime time;
		    int index = 0;
		    
		    try
		    {
		    	pstmt = DB.prepareStatement(sql.toString(), get_TrxName());
			    rs = pstmt.executeQuery();
			    
		    	while(rs.next()){
		    	    
		    		label = new Label(x-1, y, "", valuestyle);  	  
		        	sheet.addCell(label);   
		    		label = new Label(x, y, rs.getString("LineDocNo"), valuestyle);  	  
		        	sheet.addCell(label);   
		        	if(rs.getTimestamp("DateSigned") != null) {
		        		time = new DateTime(x+1, y, new java.sql.Date(rs.getTimestamp("DateSigned").getTime()), dateFormat);
		        		sheet.addCell(time);
		        	}
		        	else {
		        		label = new Label(x+1,y,"", valuestyle);
		        		sheet.addCell(label);
		        	}
		        	label = new Label(x+2,y,rs.getString("Name"), valuestyle);
		        	sheet.addCell(label);
		        	label = new Label(x+3,y,rs.getString("Description"), valuestyle);
		        	sheet.addCell(label);
		        	number = new Number(x+4,y,rs.getDouble("Summary"), numcolstyle);
		        	sheet.addCell(number);
		        	label = new Label(x+5, y, "", valuestyle);
		        	sheet.addCell(label);
		        	if(rs.getTimestamp("DateFrom") != null) {
		        		time = new DateTime(x+6, y, new java.sql.Date(rs.getTimestamp("DateFrom").getTime()), dateFormat);
		        		sheet.addCell(time);
		        	}
		        	else {
		        		label = new Label(x+6,y,"",valuestyle);
		        		sheet.addCell(label);
		        	}
		    		
		        	if(rs.getTimestamp("A_Expired_Date") != null) {
		        		time = new DateTime(x+7, y, new java.sql.Date(rs.getTimestamp("A_Expired_Date").getTime()), dateFormat);
		        		sheet.addCell(time);
		        	}
		        	else {
		        		label = new Label(x+7,y,"",valuestyle);
		        		sheet.addCell(label);
		        	}
		        	
		        	if(rs.getTimestamp("BeginningDateExecution") != null) {
		        		time = new DateTime(x+8, y, new java.sql.Date(rs.getTimestamp("BeginningDateExecution").getTime()), dateFormat);
		        		sheet.addCell(time);
		        	}
		        	else {
		        		label = new Label(x+8,y,"",valuestyle);
		        		sheet.addCell(label);
		        	}
		        	if(rs.getTimestamp("EndDateExecution") != null) {
		        		time = new DateTime(x+9, y, new java.sql.Date(rs.getTimestamp("EndDateExecution").getTime()), dateFormat);
		        		sheet.addCell(time);
		        	}
		        	else {
		        		label = new Label(x+9,y,"",valuestyle);
		        		sheet.addCell(label);
		        	}
		        	label = new Label(x+10,y,"",valuestyle);
		        	sheet.addCell(label);
		        	label = new Label(x+11,y,"",valuestyle);
		        	sheet.addCell(label);
		        	label = new Label(x+12,y,"",valuestyle);
		        	sheet.addCell(label);
		        	label = new Label(x+13,y,"",valuestyle);
		        	sheet.addCell(label);
		        	label = new Label(x+14,y,"",valuestyle);
		        	sheet.addCell(label);
		        	label = new Label(x+15,y,"",valuestyle);
		        	sheet.addCell(label);
		        	label = new Label(x+16,y,"",valuestyle);
		        	sheet.addCell(label);
		        	label = new Label(x+17,y,"",valuestyle);
		        	sheet.addCell(label);
		        	//label = new Label(x+18,y,"",valuestyle);
		        	//sheet.addCell(label);
		        	y++;
		        	index++;
		    	}
		    }
		    catch (Exception e){}
			finally
			{
				DB.close(rs, pstmt);
				rs = null; pstmt = null;
			}
		    
		    return index;	    
		}
		
		public class TempCMS_Contract
		{
			public String ContractNumber = null;
			public Timestamp ContractDate = null;
			public String C_BPartner = null;
			public String Description = null;
			public BigDecimal Summary = null;
			public String ContractType = null;
			public Timestamp DateFrom = null;
			public Timestamp A_Expired_Date = null;
			public Timestamp BeginDate = null;
			public Timestamp EndDate = null;
			public String StatusType = null;
			public String ProcurementType = null;
			public String Department = null;
			
			public BigDecimal TotalGrand = null;
			public BigDecimal PayAmt = null;
			public BigDecimal KazContent = null;
			public int CMS_Contract_ID = 0;
			
			public TempCMS_Contract() {}
		}

}
